Filtering, Sorting, Replacing, and Cleaning Data in Power BI

Course: Microsoft Power BI Training

Topic: Filtering, Sorting, Replacing, and Cleaning Data

Course Link: Microsoft Power BI Training

Demo Link: Register for a Free Course Demo

1. Introduction to Data Preparation in Power BI

Data preparation is an essential step in the Power BI workflow. Before creating reports, dashboards, visualizations, and analytical models, data must be organized into a reliable and consistent format. Real-world datasets often contain unnecessary rows, duplicate records, incorrect values, inconsistent spellings, extra spaces, missing information, and incorrectly formatted columns.

Power BI provides several tools in Power Query Editor to prepare data for analysis. Four important operations are filtering, sorting, replacing values, and cleaning data. These operations help analysts remove irrelevant information, organize records, correct errors, and improve data quality before loading the data into the Power BI data model.

For example, a sales dataset might contain transactions from several years, inconsistent product names, blank customer names, duplicate records, and sales values stored as text. By applying appropriate data preparation steps, an analyst can make the dataset more accurate and useful for business reporting.

1.1 Learning Objectives

After completing this topic, learners will be able to:

• Understand the importance of data preparation in Power BI.
• Filter rows using text, numeric, date, and conditional criteria.
• Sort records in ascending and descending order.
• Replace incorrect, inconsistent, or unwanted values.
• Remove duplicate records and unnecessary rows.
• Handle null values, blank cells, and inconsistent text.
• Clean text using Trim, Clean, and other transformation functions.
• Use Power Query's Applied Steps to review and manage transformations.
• Understand the difference between filtering, removing, replacing, and cleaning data.
• Prepare a clean dataset for reporting and visualization.

2. Understanding Power Query Editor

Power Query Editor is the data transformation environment in Power BI Desktop. It allows users to connect to data sources, inspect incoming data, apply transformation steps, and prepare tables before loading them into the model.

2.1 Opening Power Query Editor

1. Open Power BI Desktop.
2. Connect to a data source, such as Excel, CSV, SQL Server, or another supported source.
3. Choose the appropriate table or file.
4. Select Transform Data to open Power Query Editor.
5. Select the table or query you want to prepare.
6. Use the Home, Transform, and Add Column tabs to access data preparation tools.
7. After completing the transformations, select Close & Apply to load the prepared data into Power BI Desktop.

2.2 Important Areas of Power Query Editor

2.3 Why Data Preparation Matters

• Improves accuracy: Corrects inconsistent and invalid values.
• Improves reporting: Prevents misleading totals and categories.
• Reduces unnecessary data: Removes irrelevant rows and columns where appropriate.
• Improves consistency: Standardizes spelling, capitalization, and formatting.
• Supports analysis: Makes grouping, aggregation, and comparisons more reliable.
• Improves maintainability: Saves transformation steps that can be reapplied during refresh.

3. Understanding Filtering Data in Power BI

Filtering is the process of displaying or retaining only the records that satisfy specific conditions. In Power Query Editor, filtering is commonly used to remove irrelevant rows from a query before the data is loaded into the model.

For example, a company with sales records from 2022 to 2026 may want to prepare a report containing only transactions from 2026. A date filter can be applied to retain the required period.

3.1 Types of Filtering

3.2 How to Filter Text Values

1. Open Power Query Editor.
2. Select the column containing text values, such as Region or ProductName.
3. Click the filter dropdown in the column header.
4. Select or clear individual values in the list, or use the text filter options.
5. Choose the required condition, such as Equals, Does Not Equal, Begins With, Ends With, or Contains.
6. Enter the desired value and confirm the filter.
7. Review the preview to verify that only the intended records remain.

Example: A dataset contains the regions North, South, East, and West. To analyze only West region sales, filter the Region column to retain the value "West".

3.3 How to Filter Numeric Values

Numeric filters help retain rows based on numeric conditions. Common options include:

• Equals
• Does Not Equal
• Greater Than
• Greater Than or Equal To
• Less Than
• Less Than or Equal To
• Between

Example: To retain orders with sales greater than 5,000:

1. Select the SalesAmount column.
2. Open the filter dropdown.
3. Choose Number Filters.
4. Select Greater Than.
5. Enter 5000.
6. Confirm the condition and review the result.

Ensure that the SalesAmount column has a numeric data type. If it is stored as text, convert it to a suitable numeric type before applying numeric comparisons.

3.4 How to Filter Date Values

Date filters are useful for preparing monthly, quarterly, yearly, and period-based reports. Power Query offers date filtering options based on dates, ranges, and calendar periods, depending on the selected column and available interface options.

Example: To retain transactions dated from January 1, 2026, through December 31, 2026:

1. Confirm that the TransactionDate column uses a Date or Date/Time data type.
2. Open its filter dropdown.
3. Select the appropriate date filter option.
4. Set the required start and end dates.
5. Apply the filter and verify the resulting records.

For a date range with an inclusive end date, verify that the condition includes the entire intended final day, especially when the source column contains timestamps rather than dates.

3.5 Filtering Blank and Null Values

Blank or null values may indicate missing customer information, incomplete transactions, or fields that do not apply to a particular record. They should be investigated before being removed.

• Filter for null or blank values to identify incomplete records.
• Keep these rows if the missing information is important for investigation.
• Remove rows when the missing value makes the record unusable and the business rules support removal.
• Replace missing values with a suitable value only when it is logically justified.

Example: If CustomerID is mandatory for every valid transaction, rows with missing CustomerID values may need to be separated for investigation or excluded from the final reporting query. However, a missing optional description does not necessarily make the entire record invalid.

3.6 Filtering with Multiple Conditions

Multiple filters can be combined to narrow a dataset. Conditions in the same filter may use AND or OR logic, depending on the filter dialog and transformation.

Example requirement: Retain completed orders from the West region with sales greater than 5,000.

• Region equals "West".
• Status equals "Completed".
• SalesAmount is greater than 5000.

When filters are applied to separate columns, the records must satisfy the individual filters together. This effectively applies AND logic across those conditions.

3.7 Filtering Data Using Power Query M

Power Query M is the formula language used to define query transformations. The Table.SelectRows function retains rows that satisfy a specified condition.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [SalesAmount] > 5000
    )
in
    FilteredRows

This example retains rows where SalesAmount is greater than 5,000. The source expression assumes an Excel table named SalesData and a numeric SalesAmount column.

Filtering text values:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [Region] = "West"
    )
in
    FilteredRows

Filtering with multiple conditions:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [Region] = "West"
            and [SalesAmount] > 5000
            and [Status] = "Completed"
    )
in
    FilteredRows

4. Understanding Sorting Data in Power BI

Sorting arranges records in a particular order based on one or more columns. It helps users inspect data, identify the highest or lowest values, organize dates chronologically, and make records easier to understand.

Sorting in Power Query changes the order of rows in the query output. It is different from sorting a visual in a Power BI report, which controls how values are displayed in that visual.

4.1 Types of Sorting

4.2 How to Sort a Column

1. Open Power Query Editor.
2. Select the column you want to sort.
3. Open the column header dropdown or use the sorting commands on the ribbon.
4. Select Sort Ascending or Sort Descending.
5. Review the resulting order.
6. Check Applied Steps to confirm that a sorting step was added.

Example: Sorting SalesAmount in descending order helps identify the highest-value transactions first.

4.3 Sorting Text Columns

Text columns are generally sorted alphabetically according to the applicable comparison and culture rules. For example, sorting ProductName in ascending order may produce:

Keyboard
Laptop
Monitor
Mouse
Printer

Sorting text is not the same as sorting numbers. If numeric-looking values are stored as text, they may be ordered lexicographically rather than numerically. For example, text values "1", "10", and "2" may appear in that order. Convert the column to a numeric type when numerical ordering is intended.

4.4 Sorting Date Columns

Sorting a properly typed date column makes it easier to inspect transactions in chronological order. Ascending order places earlier dates first, while descending order places later dates first.

If dates are stored as text in inconsistent formats, the results may not represent chronological order. Convert the column to a Date or Date/Time data type before sorting.

4.5 Sorting by Multiple Columns

Multi-column sorting is useful when one column contains repeated values. For example, an analyst may want to sort records by Region in ascending order and then by SalesAmount in descending order within each region.

1. Sort the primary column first, such as Region ascending.
2. Apply a secondary sort, such as SalesAmount descending, using the available multi-column sorting workflow.
3. Verify that the secondary ordering applies within each group of the primary column.

When using the user interface, inspect the generated steps to ensure that the intended sort priority is preserved. Repeated sorting operations may replace or alter earlier sorting behavior depending on how the steps are created.

4.6 Sorting Using Power Query M

The Table.Sort function sorts a table by one or more columns.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    SortedRows = Table.Sort(
        Source,
        {{"SalesAmount", Order.Descending}}
    )
in
    SortedRows

Multi-column sorting example:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    SortedRows = Table.Sort(
        Source,
        {
            {"Region", Order.Ascending},
            {"SalesAmount", Order.Descending}
        }
    )
in
    SortedRows

For datasets with identical values in the sort columns, the order among tied records may not be guaranteed unless an additional tie-breaker column is included.

4.7 Important Note About Sorting in Power BI Reports

Sorting rows in Power Query does not guarantee that every report visual will display them in the same order. Visuals can apply their own sorting rules. If a report needs a specific display order, configure the visual's sort options or use a dedicated sort-order column where appropriate.

For example, month names sorted alphabetically may appear as April, August, December, and so on. To display months chronologically, use a month number or date-based sort column and configure the month-name column to sort by that column when the model supports the required relationship between them.

5. Replacing Values in Power BI

Replacing values means changing existing values in a column to different values. It is useful for correcting spelling mistakes, standardizing categories, handling selected missing values, and converting inconsistent labels into a consistent format.

For example, a Region column may contain "Wst", "West ", and "WEST". Depending on the business requirement, these values can be standardized to "West". Replacement rules should be applied carefully so that meaningful differences are not accidentally removed.

5.1 Common Uses of Replace Values

5.2 How to Replace Values

1. Open Power Query Editor.
2. Select the column containing the values you want to change.
3. Right-click the column or use the Replace Values command on the ribbon.
4. Enter the value to find.
5. Enter the replacement value.
6. Confirm the operation.
7. Review the preview and Applied Steps to ensure the transformation is correct.

Example: In a Status column, replace "Complete" with "Completed" to standardize the status labels.

5.3 Replacing Text Values

Text replacement is commonly used to standardize spelling, abbreviations, and category labels.

Example transformation:

Always inspect the original values first. A replacement that is appropriate for one column may be incorrect in another. For example, replacing every occurrence of "US" with "United States" may be inappropriate for product codes or other identifiers.

5.4 Replacing Null Values

Null values indicate missing or unavailable data. They may be replaced with a meaningful value when the business rules justify it.

Examples include:

• Replacing a missing optional product description with "Not Provided".
• Replacing a missing quantity with zero only when the source system and business definition confirm that zero is correct.
• Keeping a missing CustomerID as null and investigating the record rather than inventing an identifier.

Do not automatically replace all null values with zero. Zero means a numeric value of zero, whereas null means the value is missing or unknown. These meanings are different and can affect calculations and business decisions.

5.5 Replacing Values Using Power Query M

The Table.ReplaceValue function replaces matching values in one or more selected columns.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ReplacedValues = Table.ReplaceValue(
        Source,
        "Complete",
        "Completed",
        Replacer.ReplaceValue,
        {"Status"}
    )
in
    ReplacedValues

This example replaces the entire cell value "Complete" with "Completed" in the Status column. It does not replace every partial occurrence inside longer text values.

5.6 Replacing Part of a Text Value

When only part of a text value needs to be changed, use the appropriate text replacement option. In Power Query M, Replacer.ReplaceText can replace matching text within a string.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ReplacedText = Table.ReplaceValue(
        Source,
        "Old",
        "New",
        Replacer.ReplaceText,
        {"ProductCode"}
    )
in
    ReplacedText

Use partial replacement carefully with product codes, account numbers, and unique identifiers. A broad text replacement may unintentionally modify valid values.

5.7 Replace Values Versus Conditional Transformation

Replace Values is appropriate when known values need to be changed. Conditional transformation is more suitable when a new value depends on a rule involving one or more columns.

Example: If SalesAmount is greater than or equal to 10,000, label an order "High Value"; otherwise, label it "Standard". This is a conditional classification rather than a simple replacement of a known value.

6. Cleaning Data in Power BI

Data cleaning is the process of identifying and correcting quality issues that can affect analysis. Cleaning can include removing unnecessary spaces, eliminating non-printable characters, handling duplicates, correcting data types, standardizing values, and investigating missing information.

Power Query provides many transformations for cleaning data. The appropriate operations depend on the source system, the purpose of the analysis, and the business rules governing the data.

6.1 Common Data Quality Problems

6.2 Using Trim to Remove Extra Spaces

Trim removes leading and trailing spaces and reduces repeated ordinary spaces between words according to the text-trimming behavior used by the transformation. It is useful when names and categories contain inconsistent spacing.

Example:

Steps:

1. Select the text column.
2. Open the Transform tab.
3. Choose Format and then Trim.
4. Review the transformed values.

Trimming should generally be applied to text columns rather than numeric or date columns.

6.3 Using Clean to Remove Non-Printable Characters

Clean removes non-printable characters that may be present in imported text. Such characters can cause unexpected comparisons, matching problems, and inconsistencies in labels.

Example: A customer name imported from an external system may contain hidden control characters. Applying Clean can remove supported non-printable characters from the text.

Steps:

1. Select the relevant text column.
2. Open the Transform tab.
3. Select Format and then Clean.
4. Inspect the results, especially if the text contains unusual characters that may be meaningful.

Clean and Trim perform different tasks. Trim addresses extra spaces, while Clean targets non-printable characters. Some Unicode whitespace characters may require additional handling depending on the source and transformation.

6.4 Applying Trim and Clean Together

Applying Trim and Clean in a suitable sequence can help normalize imported text values. For example, customer names may contain extra spaces and hidden characters.

Power Query M example:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    CleanedText = Table.TransformColumns(
        Source,
        {
            {
                "CustomerName",
                each if _ is text then Text.Clean(Text.Trim(_)) else _,
                type nullable text
            }
        }
    )
in
    CleanedText

This example checks whether each CustomerName value is text before applying Trim and Clean. It preserves non-text values instead of trying to process them as strings.

6.5 Removing Duplicate Rows

Duplicate records can inflate totals, counts, and other business metrics. However, not every row with repeated values is a duplicate. Two separate orders can legitimately have the same customer, date, and sales amount.

Steps to remove duplicates:

1. Identify the column or combination of columns that uniquely identifies a valid record.
2. Select those columns in Power Query Editor.
3. Use Remove Rows and then Remove Duplicates, or use the appropriate duplicate-removal command.
4. Review the remaining records.
5. Compare row counts and business totals before and after removal.

Example: If OrderID uniquely identifies an order, duplicate OrderID values may indicate repeated records. Before removing duplicates, confirm that repeated IDs are truly invalid and not legitimate order-line records that require additional identifying columns.

When using Remove Duplicates, Power Query does not guarantee which duplicate instance will be retained in every situation. If a specific record must be kept, define a clear rule, such as retaining the latest update timestamp, and implement the required sorting and grouping logic carefully.

6.6 Removing Blank Rows and Unnecessary Rows

Blank rows and unwanted header or footer rows often appear in spreadsheets and exported reports. These rows can interfere with type conversion and calculations.

Useful options include:

• Remove Blank Rows: Removes rows that Power Query identifies as blank.
• Remove Top Rows: Removes a specified number of rows from the beginning of a table.
• Remove Bottom Rows: Removes a specified number of rows from the end of a table.
• Remove Errors: Removes rows containing errors in the selected column or columns, depending on the command used.
• Filter Rows: Retains records that satisfy a condition.

Do not remove rows merely because they appear unusual. Check whether they contain valid totals, notes, exceptions, or important business records before excluding them.

6.7 Handling Missing and Error Values

Missing values and error values are not the same. A null value represents missing or absent information, whereas an error indicates that Power Query could not successfully evaluate or convert a value.

• Filter for null values to identify missing data.
• Use Replace Values when an appropriate replacement is known.
• Use Replace Errors when an error should be replaced with a defined value.
• Use Remove Errors only when the affected records should legitimately be excluded.
• Investigate conversion errors before deciding how to handle them.

Example: If the Quantity column contains "Ten" and "15", converting the entire column to a number may produce an error for "Ten". Correct the source value, apply a valid mapping, or route the invalid record for review rather than silently deleting it.

6.8 Standardizing Text Case

Text values may differ only in capitalization, such as "north", "North", and "NORTH". Standardizing case can improve consistency in labels and grouping.

Power Query's Format options include operations such as Uppercase, Lowercase, and Capitalize Each Word, where available for the selected column.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    StandardizedRegion = Table.TransformColumns(
        Source,
        {
            {
                "Region",
                each if _ is text then Text.Proper(Text.Trim(_)) else _,
                type nullable text
            }
        }
    )
in
    StandardizedRegion

This example trims spaces and converts text to a proper-case format. Apply case conversion only when it is suitable for the field. It may be inappropriate for case-sensitive identifiers, email addresses requiring exact preservation, or product codes with specific capitalization rules.

6.9 Cleaning and Standardizing Identifiers

Identifiers such as CustomerID, ProductCode, and PostalCode require special care. These fields may look numeric but function as labels rather than quantities.

• Keep leading zeros when they are meaningful.
• Avoid summing identifier columns.
• Standardize spaces and punctuation only when allowed by the identifier format.
• Preserve case when identifiers are case-sensitive.
• Check whether the identifier is unique before using it as a key.

For example, a postal code such as "01234" should generally remain text if converting it to a number would remove the leading zero.

7. Understanding the Difference Between Filtering, Sorting, Replacing, and Cleaning

These operations are related, but each serves a different purpose in the data preparation workflow.

8. Combining Transformations in a Practical Workflow

In real-world projects, data preparation usually involves multiple transformations rather than a single operation. A consistent sequence makes the query easier to understand, maintain, and refresh.

8.1 Recommended Workflow

1. Connect to the source: Import the sales, customer, inventory, or transaction dataset.
2. Inspect the data: Review column names, data types, sample values, nulls, and errors.
3. Remove unnecessary rows: Exclude repeated headers, blank rows, and irrelevant records when justified.
4. Set data types: Ensure dates are dates, amounts are numeric, and identifiers use appropriate types.
5. Clean text columns: Apply Trim and Clean where needed.
6. Standardize values: Correct spelling errors and inconsistent categories.
7. Handle missing and error values: Investigate and apply business rules.
8. Identify duplicates: Use the appropriate unique key or composite key.
9. Filter the dataset: Retain the required records for the analysis.
10. Sort if necessary: Apply sorting for inspection or where downstream operations require it.
11. Validate the output: Compare row counts, totals, distinct counts, and sample records.
12. Load the data: Select Close & Apply after checking the transformations.

This sequence is a general guideline rather than a strict rule. Some sources require filtering early to reduce processing, while other workflows require data types or normalization before filters and comparisons behave correctly.

8.2 Example Sales Dataset

Consider the following sample data before cleaning:

8.3 Cleaning the Dataset Step by Step

Step 1: Clean CustomerName. Apply Trim to remove unnecessary leading and trailing spaces. Investigate the missing customer name rather than inventing a value.

Step 2: Standardize Region. Replace "Wst" with "West" if this is the correct interpretation of the source value.

Step 3: Standardize Status. Replace "Complete" with "Completed" if both labels represent the same business status.

Step 4: Investigate duplicate OrderID values. Confirm whether the repeated OrderID 1003 represents an accidental duplicate or legitimate repeated order lines. Remove it only if the business rule confirms that it is a duplicate record.

Step 5: Validate data types. Ensure SalesAmount is numeric and OrderID is represented appropriately for its business purpose.

Step 6: Filter records. If the report needs completed orders only, filter Status to "Completed". If the report needs transactions above 5,000, apply a numeric filter to SalesAmount.

Step 7: Sort records. Sort SalesAmount in descending order to inspect the highest-value transactions.

Step 8: Validate results. Check the final row count, total sales, region labels, statuses, and duplicate identifiers.

8.4 Example Power Query M Transformation

The following example demonstrates several common operations in one query. It assumes an Excel table named SalesData with columns named CustomerName, Region, Status, and SalesAmount. It cleans text, standardizes known labels, filters completed orders with sales above 5,000, and sorts the result.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    CleanedCustomerName = Table.TransformColumns(
        Source,
        {
            {
                "CustomerName",
                each if _ is text then Text.Clean(Text.Trim(_)) else _,
                type nullable text
            }
        }
    ),
    StandardizedRegion = Table.ReplaceValue(
        CleanedCustomerName,
        "Wst",
        "West",
        Replacer.ReplaceValue,
        {"Region"}
    ),
    StandardizedStatus = Table.ReplaceValue(
        StandardizedRegion,
        "Complete",
        "Completed",
        Replacer.ReplaceValue,
        {"Status"}
    ),
    ChangedTypes = Table.TransformColumnTypes(
        StandardizedStatus,
        {
            {"SalesAmount", type number}
        }
    ),
    FilteredRows = Table.SelectRows(
        ChangedTypes,
        each [Status] = "Completed"
            and [SalesAmount] > 5000
    ),
    SortedRows = Table.Sort(
        FilteredRows,
        {{"SalesAmount", Order.Descending}}
    )
in
    SortedRows

Important: This example assumes the source data can be converted to a numeric SalesAmount column. If invalid text or other conversion errors are present, handle those values before or during type conversion. Also, the example does not remove duplicates because that operation should depend on the actual business key and duplicate-removal policy.

9. Working with Applied Steps

Every transformation in Power Query is recorded in the Applied Steps pane. These steps make the cleaning process repeatable and easier to audit.

9.1 Why Applied Steps Are Important

• They document how the source data was transformed.
• They allow users to inspect the order of operations.
• They make it possible to edit or remove a transformation.
• They are reapplied when the query refreshes, subject to the source data and query behavior.
• They help diagnose problems when a later transformation depends on an earlier step.

9.2 Editing and Deleting Steps

1. Open Power Query Editor.
2. Select the relevant query.
3. Locate the Applied Steps pane.
4. Select a step to inspect the result at that point.
5. Use the step's available settings or context menu to edit or delete it.
6. Review all subsequent steps because they may depend on the removed or changed step.

For example, deleting a step that renames a column may cause later steps referencing the new column name to fail. Always review the query after editing transformation history.

9.3 Understanding Transformation Order

The order of transformations can affect the final output. For example, replacing "Wst" with "West" before filtering for "West" ensures that both original forms can be included in the filtered results.

Similarly, converting a date column using the correct locale before applying a date filter helps ensure that the intended dates are retained. A filter applied before correcting the data may exclude valid records or produce errors.

10. Common Errors and Troubleshooting

10.1 How to Investigate Data Errors

1. Identify the column and transformation where the error first appears.
2. Inspect the original source value and its intended meaning.
3. Check the data type and regional formatting.
4. Determine whether the problem affects one row or many rows.
5. Correct the source or create an appropriate transformation.
6. Review the resulting row count and important totals.
7. Refresh the query to verify that the fix works with the available data.

11. Best Practices for Data Cleaning in Power BI

• Understand the business meaning: Do not replace or remove data without knowing what the values represent.
• Preserve source data: Keep the original source unchanged where possible and perform transformations in Power Query.
• Use meaningful query and step names: Clear names make the transformation process easier to maintain.
• Filter intentionally: Confirm that the excluded records are outside the report's scope.
• Use appropriate data types: Correct types make filters, calculations, and comparisons more reliable.
• Clean text consistently: Apply Trim, Clean, and standardization rules to relevant columns.
• Handle nulls carefully: Missing values are not automatically zero or invalid records.
• Verify duplicates: Identify the appropriate unique key before removing repeated rows.
• Avoid overly broad replacements: Prefer precise rules and maintain a mapping for many inconsistent category values.
• Validate results: Compare row counts, distinct counts, totals, and representative records before and after transformations.
• Consider query folding: When working with supported data sources, some transformations can be pushed back to the source system. Check folding behavior when performance matters.
• Test refresh behavior: Ensure that transformation steps work with newly arriving values, not just the current preview.
• Document important decisions: Record why records are excluded, why values are replaced, and how duplicates are identified.

12. Practical Exercises

Exercise 1: Filter Sales Records

Objective: Retain only sales transactions greater than 10,000.

1. Import a sales dataset into Power BI.
2. Open Power Query Editor.
3. Confirm that SalesAmount is a numeric column.
4. Apply a Greater Than filter with the value 10000.
5. Verify that every remaining record satisfies the condition.

Expected result: The query contains only records whose SalesAmount is greater than 10,000.

Exercise 2: Sort Customer Records

Objective: Arrange customers alphabetically and inspect the resulting order.

1. Select the CustomerName column.
2. Apply Sort Ascending.
3. Review the order of names.
4. Check for leading spaces or inconsistent text values if the order looks incorrect.

Expected result: Customer names are arranged in ascending text order after accounting for the relevant comparison rules.

Exercise 3: Replace Incorrect Categories

Objective: Standardize region names.

Example values: "Wst", "West ", "WEST", and "West".

1. Inspect the distinct values in the Region column.
2. Apply Trim where needed.
3. Replace known incorrect labels.
4. Standardize case if suitable.
5. Verify the resulting distinct values.

Expected result: All values that represent the same region follow a consistent naming rule.

Exercise 4: Remove Duplicates Safely

Objective: Identify duplicate orders without removing legitimate transactions.

1. Inspect the OrderID column and relevant order-line fields.
2. Determine which columns define a unique record.
3. Identify repeated records.
4. Confirm whether they are accidental duplicates or valid separate transactions.
5. Remove only the records that meet the approved duplicate-removal rule.
6. Compare row counts and sales totals before and after the operation.

Expected result: Confirmed duplicate records are handled according to the business rule, and valid records are retained.

Exercise 5: Clean a Customer Dataset

Objective: Improve the quality of customer names and region labels.

• Remove leading and trailing spaces from customer names.
• Remove applicable non-printable characters.
• Standardize region labels.
• Investigate missing customer names.
• Check whether CustomerID is unique.
• Validate the final number of records.

Exercise 6: Build a Complete Transformation Workflow

Objective: Combine filtering, sorting, replacing, and cleaning into one Power Query workflow.

1. Import a sales dataset.
2. Inspect and correct data types.
3. Clean selected text columns.
4. Replace known inconsistent category values.
5. Investigate missing data and duplicate records.
6. Filter records according to the reporting requirements.
7. Sort the data for inspection.
8. Review Applied Steps.
9. Load the data into Power BI Desktop.
10. Create a simple table visual to verify the prepared results.

13. Interview Questions and Answers

Q1. What is Power Query in Power BI?

Answer: Power Query is the data connection and transformation tool used to import, clean, reshape, and prepare data before loading it into the Power BI data model.

Q2. What is data filtering?

Answer: Filtering retains only records that satisfy specified conditions. For example, a numeric filter can retain sales transactions greater than 5,000.

Q3. What is the difference between filtering and sorting?

Answer: Filtering changes which rows remain in the result, whereas sorting changes the order in which rows appear. Sorting does not normally remove records.

Q4. What is Replace Values used for?

Answer: Replace Values changes selected values in a column. It can correct spelling mistakes, standardize categories, and handle known placeholder values.

Q5. What is the difference between Trim and Clean?

Answer: Trim removes extra spaces according to text-trimming behavior. Clean removes non-printable characters handled by the transformation. They solve different text-cleaning problems.

Q6. Why should data be cleaned before creating reports?

Answer: Cleaning improves data consistency and reliability, reduces avoidable errors, and helps ensure that report calculations and categories accurately represent the underlying information.

Q7. What are null values?

Answer: Null values represent missing or absent information. They should be investigated and handled according to the business rules instead of being automatically replaced with zero.

Q8. What is the purpose of removing duplicates?

Answer: Duplicate removal eliminates repeated records when those records are confirmed to be duplicates. The correct unique key must be identified before records are removed.

Q9. What is the difference between Remove Errors and Replace Errors?

Answer: Remove Errors excludes rows containing errors in the relevant column selection. Replace Errors substitutes an error with a specified value. The appropriate choice depends on the business requirement.

Q10. What is Power Query M?

Answer: Power Query M is the formula language used to define data extraction and transformation steps. Functions such as Table.SelectRows, Table.Sort, Table.ReplaceValue, and Table.TransformColumns support common data preparation tasks.

Q11. What are Applied Steps?

Answer: Applied Steps records the sequence of transformations applied to a query. Users can inspect, modify, or remove steps and review how each transformation affects the data.

Q12. Does sorting in Power Query guarantee the same order in every report visual?

Answer: No. Report visuals can apply their own sorting rules. Configure the visual's sort settings or use a suitable sort-order column when a specific display order is required.

Q13. Why can duplicate removal be dangerous?

Answer: Records that look similar may represent separate valid transactions. Removing duplicates without confirming the business key can delete legitimate records and change totals.

Q14. What is the difference between Replace Value and Replace Text?

Answer: Replacing a value targets a matching cell value, while text replacement can change matching portions within a text string. The exact behavior depends on the replacement operation selected.

Q15. What is query folding?

Answer: Query folding occurs when Power Query translates supported transformations into operations executed by the source system. It can improve performance, although not every source or transformation supports folding.

14. Quick Revision Checklist

• Filtering retains rows that meet defined conditions.
• Sorting organizes records in ascending or descending order.
• Replacing values standardizes or corrects known values.
• Trim removes unwanted spaces according to its trimming behavior.
• Clean removes non-printable characters handled by the transformation.
• Null values and errors require different handling strategies.
• Duplicate removal must be based on a valid business key.
• Data types should be checked before numeric and date filtering.
• Applied Steps records the transformation history.
• Power Query M supports repeatable data preparation operations.
• Validate row counts, totals, distinct values, and sample records.
• Use Close & Apply to load the prepared data into Power BI Desktop.
• Configure report visual sorting separately when needed.

15. Conclusion

Filtering, sorting, replacing, and cleaning data are essential skills for preparing reliable datasets in Microsoft Power BI. Filtering helps retain relevant records, sorting organizes information, replacing values corrects inconsistencies, and cleaning improves the quality of text and other fields. When these operations are combined with correct data types, careful duplicate handling, and systematic validation, they create a stronger foundation for meaningful reports and business insights.

Successful data preparation is not simply about making a dataset look tidy. It requires understanding the meaning of each field, protecting valid records, applying consistent business rules, and ensuring that every transformation produces the intended result. Practicing these operations in Power Query Editor helps learners build efficient and repeatable Power BI workflows.

16. Additional Learning Resources

• Explore Microsoft Power BI Training at JustAcademy
• Register for a JustAcademy Course Demo
• Visit the JustAcademy Website
• Microsoft Learn: Power Query Documentation
• Microsoft Learn: Power Query User Interface
• Microsoft Learn: Power Query M Formula Language